import numpy as np
import pandas as pd
import math
import matplotlib.pyplot as plt
import pandas_profiling
#%matplotlib inline
pd.options.display.max_columns = None
pd.set_option('display.float_format', lambda x: '%.4f' % x)

df_train = pd.read_csv("train.csv")
df_test = pd.read_csv("testA.csv")
#使用pandas的profile不难看出完美共线性的指标，分别去掉
df_train = df_train.drop(["n0",'n3'],axis=1)
df_test = df_test.drop(["n0",'n3'],axis=1)
total_data = pd.concat([df_train, df_test])
total_data_FE = total_data.copy()

grade_dic = {"E":4, "D":3, "A":0, "C":2, "B":1, "F":5, "G":6}
total_data_FE.grade = total_data_FE.grade.map(grade_dic,na_action="ignore")
total_data_FE.grade.value_counts()
total_data_FE.grade.astype('category')

emp_dic = {"2 years":2, "5 years":5, "8 years":8, "10+ years":10, "7 years":7,"9 years":9, "1 year":1, "3 years":3, "< 1 year":0, "4 years":4, "6 years":6}
total_data_FE.employmentLength = total_data_FE.employmentLength.map(emp_dic,na_action="ignore")
total_data_FE.employmentLength.value_counts()
total_data_FE.employmentLength.astype('category')

subgrade_dic = {"A1":0,"A2":1,"A3":2,"A4":3,"A5":4,"B1":5,"B2":6,"B3":7,"B4":8,"B5":9,"C1":10,"C2":11,"C3":12,"C4":13,"C5":14,"D1":15,"D2":16,"D3":17,"D4":18,"D5":19,"E1":20,"E2":21,"E3":22,"E4":23,"E5":24,"F1":25,"F2":26,"F3":27,"F4":28,"F5":29,"G1":30,"G2":31,"G3":32,"G4":33,"G5":34}
total_data_FE.subGrade = total_data_FE.subGrade.map(subgrade_dic,na_action="ignore")
total_data_FE.subGrade.value_counts()
total_data_FE.subGrade.astype('category')

#时间数据的FE
from datetime import datetime
t1 = pd.to_datetime(total_data_FE.issueDate)
t2 = pd.to_datetime(total_data_FE.earliesCreditLine)
num_years = round(abs((t1-t2).apply(lambda x : x.days))/365,0)

#issueDate_now = datetime.now().strftime("%Y-%m-%d") - t1
issueDate_now = t1.apply(lambda x : datetime.now() - x)
issueDate_now_year = issueDate_now.apply(lambda x : x.days)

earliesCreditLine_now = t2.apply(lambda x : datetime.now() - x)
earliesCreditLine_now_year = earliesCreditLine_now.apply(lambda x : x.days)


total_data_FE["num_years"] = num_years
total_data_FE["issueDate_now_year"] = issueDate_now_year
total_data_FE["earliesCreditLine_now_year"] = earliesCreditLine_now_year


#特征构造，计算title长度
title_len = []
for title in total_data_FE["title"]:
  if title>=0:
    li = len(str(math.ceil(title)))
    title_len.append(li)
  else:
    title_len.append(0)

# title_str = total_data_FE["title"].apply(lambda x: math.floor(x) if x != np.nan)
# title_str


#特征构造，计算employmentTitle长度
employmentTitle_len = []
for employmentTitle in total_data_FE["employmentTitle"]:
  if employmentTitle>=0:
    li = len(str(math.ceil(employmentTitle)))
    employmentTitle_len.append(li)
  else:
   employmentTitle_len.append(0)


total_data_FE["employmentTitle_len"] = employmentTitle_len
total_data_FE["title_len"] = title_len

#特征构造，ficoRange
ficoRange = total_data_FE.ficoRangeHigh - total_data_FE.ficoRangeLow
ficoRange

total_data_FE["ficoRange"] = ficoRange
total_data_FE.ficoRange.astype('category')
total_data_FE

total_data_FE = total_data_FE.drop("policyCode",axis=1)

#债务负值异常值处理
total_data_FE.dti[total_data_FE.dti<0] = 0

# 业务变量构造
# 余额 revolBal+totalAcc
# 信用已用额度 totalAcc-openAcc
# 年还款 loanAmnt/term
# 年收入/年还款 annualIncome/（loanAmnt/term）
# 其他衍生变量构造（乘除法）不一一描述，例如债务与年收入 dti*annualIncome，利息与期数倍数 interestRate*term
revolBal_p_totalAcc = total_data_FE.revolBal + total_data_FE.totalAcc
totalAcc_m_openAcc = total_data_FE.totalAcc - total_data_FE.openAcc
loanAmnt_term = total_data_FE.loanAmnt/total_data_FE.term
annualIncome_loanAmnt_term = total_data_FE.annualIncome/loanAmnt_term
debt = total_data_FE.dti*total_data_FE.annualIncome
pro = total_data_FE.interestRate*total_data_FE.term
installment_annualIncome = total_data_FE.installment/total_data_FE.annualIncome
loanAmnt_applicationType = total_data_FE.loanAmnt/(1+total_data_FE.applicationType)


total_data_FE["revolBal_p_totalAcc"] = revolBal_p_totalAcc
total_data_FE["totalAcc_m_openAcc"] = totalAcc_m_openAcc
total_data_FE["loanAmnt_term"] = loanAmnt_term
total_data_FE["annualIncome_loanAmnt_term"] = annualIncome_loanAmnt_term
total_data_FE["debt"] = debt
total_data_FE["pro"] = pro
total_data_FE["installment_annualIncome"] = installment_annualIncome
total_data_FE["loanAmnt_applicationType"] = loanAmnt_applicationType

#业务变量衍生构造
eny_num = total_data_FE.earliesCreditLine_now_year/(total_data_FE.num_years)
int_sub = total_data_FE.interestRate/(total_data_FE.subGrade)
pd = total_data_FE.pro*total_data_FE.dti
top1 = total_data_FE.issueDate_now_year*total_data_FE.ficoRangeHigh

total_data_FE["eny_num"] = eny_num
total_data_FE["int_sub"] = int_sub
total_data_FE["pd"] = pd
total_data_FE["top1"] = top1

#处理公共记录
rec = total_data_FE.pubRec - total_data_FE.pubRecBankruptcies
rec_rate = total_data_FE.pubRecBankruptcies/(1+total_data_FE.pubRec)

total_data_FE["rec"]=rec
total_data_FE["rec_rate"]=rec_rate

#处理group feature
n_sum = total_data_FE.n1 +total_data_FE.n2+total_data_FE.n4+total_data_FE.n5+total_data_FE.n6+total_data_FE.n7+total_data_FE.n8+total_data_FE.n9+total_data_FE.n10+total_data_FE.n11+total_data_FE.n12+total_data_FE.n13+total_data_FE.n14
n_mean = n_sum/14

total_data_FE["n_sum"] = n_sum
total_data_FE["n_mean"] = n_mean

def num_cols(x):
    a = x.apply(lambda x: x.nunique())
    num_cols = a[a >40].index.tolist()
    return num_cols
num_cols = num_cols(total_data_FE)

num_cols.remove("id")
num_cols.remove("issueDate")
num_cols.remove("earliesCreditLine")
num_cols

#log处理
for col in num_cols:
  total_data_FE[col] = total_data_FE[col].apply(lambda x: math.log(np.float(x)+1))


new_train_data_FE = total_data_FE[total_data_FE['isDefault'].notnull()]
new_test_data_FE = total_data_FE[total_data_FE['isDefault'].isnull()]
new_train_data_FE.to_csv("new_train_data_FE8.csv",header=1,index=0)
new_test_data_FE.to_csv("new_test_data_FE8.csv",header=1,index=0)